/*
 * Created on Sep 29, 2006
 */
package com.scalar.chiptrack.operations.distributor.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.HashMap;
import java.util.Calendar;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.distributor.DistSalesSearch;
import com.scalar.chiptrack.operations.distributor.DistributorInventory;
import com.scalar.chiptrack.operations.distributor.DistLineItem;
import com.scalar.chiptrack.operations.distributor.forms.DistributorSearchForm;
import com.scalar.chiptrack.operations.distributor.forms.DistributorSalesForm;
import com.scalar.chiptrack.operations.distributor.forms.DistributorInventoryForm;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.utils.StringUtils;

/**
 * @author ravi
 */
public class DistributorDao
{
    public static ArrayList<DistributorInventory> loadDistributorInventory( int productId, int distributor_Id , int distSalesOrderId, String str ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        ArrayList<DistributorInventory> inventoryList = new ArrayList<DistributorInventory>();
        DistributorInventory inventory = null;
        int modifyStatus = 0;
        int shippedQty = 0;

        try
        {
            conn = DataSource.getConnection();
            /*if (modifyStatus ==1)
            {
                pstmt = conn.prepareStatement( "select Lot_number, Convert(Varchar, Date_In, 101 ) as Date_In, Date_code, SUM( QTY_IN ) AS Qty_in, ISNULL( SUM( SHIPPED_QTY ), 0) AS SHIPPED_QTY, PULLED_QTY from Distributor_Inventory_lot, product where Distributor_Inventory_lot.ProcessStep_id = product.ProcessStep_Id and Customer_Id = ? and Product_Id = ? and ( Qty_in - ISNULL( Shipped_Qty, 0 ) ) > 0 GROUP BY LOT_NUMBER, DATE_IN, DATE_CODE " );
            }
            else
            {
                pstmt = conn.prepareStatement( "select Lot_number, Convert(Varchar, Date_In, 101 ) as Date_In, Date_code, SUM( QTY_IN ) AS Qty_in, ISNULL( SUM( SHIPPED_QTY ), 0) AS SHIPPED_QTY from Distributor_Inventory_lot, product where Distributor_Inventory_lot.ProcessStep_id = product.ProcessStep_Id and Customer_Id = ? and Product_Id = ? and Dist_Shipping_Lot_Table.lot_number= Distributor_Inventory_lot.lot_number and ( Qty_in - ISNULL( Shipped_Qty, 0 ) ) >= 0 GROUP BY LOT_NUMBER, DATE_IN, DATE_CODE " );
            }*/
            cstmt = conn.prepareCall ( "{ CALL SP_GET_DIST_SO_INVENTORY_DETAILS( ?, ?, ?, ? ) }" );
            cstmt.setInt (1, distSalesOrderId );
            cstmt.setInt( 2, productId );
            cstmt.setInt( 3, distributor_Id );
            cstmt.setString( 4, str );
            rset = cstmt.executeQuery();

            while( rset.next() )
            {
                inventory = new DistributorInventory();
                inventory.setLot_number( rset.getString( "Lot_number" ) );
                inventory.setDate_in( rset.getString( "Date_In" ) );
                inventory.setDateCode( rset.getString( "Date_code" ) );
                inventory.setQty_in( rset.getInt( "Qty_in" ) );
                shippedQty = rset.getInt( "Shipped_Qty" );
                inventory.setQty_pulled(rset.getInt("pulled_qty"));
                inventory.setLot_id(rset.getInt("Lot_Id"));
                //inventory.setQty_pulled(  );
                inventory.setCsLitId( rset.getInt("CSLIT_ID") );
                inventory.setDistributorId( rset.getInt("DISTRIBUTOR_ID"));
                inventory.setProductId( rset.getInt("PRODUCT_ID"));
                inventory.setDistributorName( rset.getString("DISTRIBUTOR_NAME"));
                inventory.setPartNumber( rset.getString("PART_NUMBER"));
                inventory.setSoNo( rset.getString("SALES_ORDER_NO"));
                inventory.setUnitCost( rset.getDouble("UNIT_COST"));
                inventory.setQty_available( inventory.getQty_in() - shippedQty );

                inventoryList.add( inventory );
            }
        }
        catch( Exception e )
        {   e.printStackTrace();
            throw new ScalarException( "Could not retrieve Distributor Inventory List from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return inventoryList;
    }

    /**
     * @param searchForm
     * @return  loadDistributorSalesSearch
     */
    public static ArrayList loadDistributorSalesSearch(DistributorSearchForm searchForm) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        ArrayList salesOrderList = new ArrayList();
        DistSalesSearch salesSearch = null;

        try
        {
            conn = DataSource.getConnection();

            cstmt = conn.prepareCall( "{ call SP_SEARCH_DISTRIBUTOR_SALES( ?,?,?,? ) }" );
            cstmt.setInt( 1, searchForm.getDistributorId() );
            cstmt.setInt( 2, searchForm.getCustomerId() );
            cstmt.setInt( 3, searchForm.getManufacturingPartNo() );
            cstmt.setInt( 4, new Integer(searchForm.getYear()).intValue() );
            rset = cstmt.executeQuery();

            while( rset.next() )
            {
                salesSearch = new DistSalesSearch();
                salesSearch.setDistributor ( rset.getString( "Distributor" ) );
                salesSearch.setSalesOrderId ( rset.getInt( "Dist_SalesOrder_Id" ) );
                salesSearch.setSalesOrder ( rset.getString( "Dist_SalesOrder_No" ) );
                salesSearch.setCustomer ( rset.getString( "Customer" ) );
                salesSearch.setCustomerPo( rset.getString( "Customer_PO" ) );
                salesSearch.setShipDate( rset.getString( "Actual_Ship_Date" ) );
                salesSearch.setStatus( rset.getString("status"));
                salesOrderList.add( salesSearch );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Distributor Inventory List from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return salesOrderList;
    }

    public static void saveDistSalesOrderDetails ( DBTransaction dbTransaction , DistributorSalesForm salesForm, User user ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        boolean success         = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall ( "{ CALL SP_CREATE_UPDATE_DIST_SO_DETAILS( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );

            cstmt.setInt    ( 1, salesForm.getDistSalesOrderId() );
            cstmt.setString ( 2, salesForm.getSalesOrderNo() );
            cstmt.setString ( 3, salesForm.getCustomerPO() );
            cstmt.setString ( 4, salesForm.getDistributorId() );
            cstmt.setString ( 5, salesForm.getCustomer_Id() );
            cstmt.setString ( 6, salesForm.getNewEndCustomerName());
            cstmt.setString ( 7, salesForm.getSalesOrderDate() );
            cstmt.setString ( 8, salesForm.getShipDate() );
            cstmt.setString ( 9, user.getUserName() );
            cstmt.setString (10, salesForm.getStatus());
            cstmt.setInt    (11, (salesForm.getStatus() != null && salesForm.getStatus().equalsIgnoreCase("cancel") ? 1 :0));
            cstmt.registerOutParameter( 12, Types.INTEGER );
            cstmt.executeUpdate();
            salesForm.setDistSalesOrderId ( cstmt.getInt( 12 ) );
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not Save the Distribute Sales Order Details into database.");
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if (conn != null)
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {System.out.println("Sql exception ");}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static void saveDistSOLineItemDetails ( DBTransaction dbTransaction ,  DistributorSalesForm salesForm ) throws ScalarException
    {

        Connection conn 		    = null;
        CallableStatement cstmt     = null;
        boolean success             = false;
        Iterator lineItemIT         = salesForm.getLineItemList().iterator();
        DistLineItem distLineItem   = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall( "{ CALL SP_CREATE_UPDATE_DIST_SO_LINEITEM_DETAILS( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );

            while( lineItemIT.hasNext() )
            {
                distLineItem = ( DistLineItem ) lineItemIT.next();

                if( distLineItem.getProductId() > 0 )
                {
                    cstmt.setInt    ( 1, salesForm.getDistSalesOrderId() );
                    cstmt.setInt    ( 2, distLineItem.getLineItem_Id() );
                    cstmt.setInt    ( 3, distLineItem.getLineItemNo() );
                    cstmt.setInt    ( 4, distLineItem.getProductId() );
                    cstmt.setInt    ( 5, distLineItem.getQty_req() );
                    cstmt.setString ( 6, distLineItem.getReq_date() );
                    cstmt.setInt    ( 7, distLineItem.getQtyShip() );
                    cstmt.setString ( 8, distLineItem.getShipDate() );
                    cstmt.setDouble ( 9, distLineItem.getUnitCost() );
                    cstmt.setDouble ( 10, distLineItem.getExtCost() );
                    cstmt.setDouble ( 11, distLineItem.getReSalePrice() );
                    cstmt.setDouble ( 12, distLineItem.getExtReSalePrice() );
                    cstmt.setString ( 13, distLineItem.getUserDefinedField1() );
                    cstmt.setString ( 14, distLineItem.getUserDefinedField1() );
                    cstmt.registerOutParameter( 15, Types.INTEGER );
                    cstmt.executeUpdate();
                    distLineItem.setLineItem_Id( cstmt.getInt( 15 ) );
                }
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            Logger.error( "Error while saving the Distributor Sales Order Line Item details", e );
            throw new ScalarException ("Error while saving the Distributor Sales Order Line Item details");
        }
        finally
        {
            if( dbTransaction == null )
            {
                if( success == true )
                {
                    try
                    {
                        conn.commit();
                    }
                    catch( Exception e ) { }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch( Exception e ) { }
                }
            }

            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }

    public static void saveDistSOLineItemInventoryDetails ( DBTransaction dbTransaction ,  DistributorSalesForm salesForm, HashMap lineItemWisePulledLotsMap ) throws ScalarException
    {

        Connection conn 		    = null;
        CallableStatement cstmt     = null;
        boolean success             = false;
        ArrayList lineItemList      = salesForm.getLineItemList();
        ArrayList pulledLotsList    = null;
        DistLineItem distLineItem   = null;
        DistributorInventory pulledInventory = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if( lineItemWisePulledLotsMap != null && lineItemList != null && lineItemList.size() > 0 )
            {
                cstmt = conn.prepareCall( "{ CALL SP_CREATE_UPDATE_DIST_SO_INVENTORY_DETAILS( ?, ?, ?, ?, ?, ?, ?, ?) }" );

                for( int i = 0; i < lineItemList.size() ; i++ )
                {

                    distLineItem = ( DistLineItem ) lineItemList.get( i );
                    if( distLineItem != null && ( lineItemWisePulledLotsMap.containsKey( distLineItem.getLineItemNo() + "" ) ) )
                    {
                        pulledLotsList  = ( ArrayList ) lineItemWisePulledLotsMap.get( distLineItem.getLineItemNo() + "" );

                        if( pulledLotsList != null && pulledLotsList.size() > 0 )
                        {
                            for( int j = 0; j < pulledLotsList.size() ; j++ )
                            {
                                pulledInventory = ( DistributorInventory ) pulledLotsList.get( j );
                                if ( pulledInventory.getQty_pulled() > 0 )
                                {
                                    cstmt.setInt (1, salesForm.getDistSalesOrderId());
                                    cstmt.setString ( 2, salesForm.getDistributorId() );
                                    cstmt.setInt    ( 3, distLineItem.getLineItem_Id() );
                                    cstmt.setInt    ( 4, distLineItem.getProductId() );
                                    cstmt.setInt    ( 5, pulledInventory.getLot_id() );
                                    cstmt.setString ( 6, pulledInventory.getLot_number() );
                                    cstmt.setInt    ( 7, pulledInventory.getQty_pulled() );
                                    cstmt.setInt    ( 8, pulledInventory.getCsLitId() );
                                    cstmt.addBatch();
                                }   
                            }
                        }
                    }
                }
                cstmt.executeBatch();
                cstmt.clearBatch();
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            Logger.error( "Error while saving the Distributor Sales Order Inventory details", e );
            throw new ScalarException ("Error while saving the Distributor Sales Order Inventory details");
        }
        finally
        {
            if( dbTransaction == null )
            {
                if( success == true )
                {
                    try
                    {
                        conn.commit();
                    }
                    catch( Exception e ){ }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch( Exception e ){ }
                }
            }

            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }

    public static void loadCompleteBumpReleaseDetails( DistributorSalesForm salesForm, int distSalesOrderId ) throws ScalarException
    {
        try
		{
            salesForm.setDistSalesOrderId( distSalesOrderId );
            loadDistSalesOrderDetails( null, salesForm );
            salesForm.setLineItemList( loadDistSOLineItems( null, salesForm.getDistSalesOrderId() ) );
        }
		catch( Exception e )
		{
            e.printStackTrace();
			throw new ScalarException ("Error while retreiving the Distributor SO details");
		}
    }

    public static void loadDistSalesOrderDetails( DBTransaction dbTransaction, DistributorSalesForm salesForm ) throws ScalarException
    {
		Connection conn 		    = null;
		PreparedStatement pstmt     = null;
		ResultSet rs 			    = null;

		try
		{
            if( dbTransaction == null )
            {
                conn	= DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt 	= conn.prepareStatement( "SELECT DIST_SALESORDER_NO, CUSTOMER_PO, DISTRIBUTOR_ID, CUSTOMER_ID, CONVERT(VARCHAR, SALESORDER_DATE, 101) AS SALESORDER_DATE, CONVERT(VARCHAR, ACTUAL_SHIP_DATE, 101) AS ACTUAL_SHIP_DATE, NEW_END_CUSTOMER_NAME, STATUS FROM DIST_SALES_ORDER WHERE DIST_SALESORDER_ID = ?" );
            pstmt.setInt( 1, salesForm.getDistSalesOrderId() );
			rs 		= pstmt.executeQuery();
			if ( rs.next() )
			{
                salesForm.setSalesOrderNo   ( rs.getString( "DIST_SALESORDER_NO") );
                salesForm.setCustomerPO     ( rs.getString( "CUSTOMER_PO") );
                salesForm.setDistributorId  ( rs.getString( "DISTRIBUTOR_ID") );
                salesForm.setCustomer_Id    ( rs.getString( "CUSTOMER_ID") );
                salesForm.setSalesOrderDate ( rs.getString( "SALESORDER_DATE") );
                salesForm.setShipDate       ( rs.getString( "ACTUAL_SHIP_DATE") );
                salesForm.setNewEndCustomerName ( rs.getString("NEW_END_CUSTOMER_NAME"));
                salesForm.setStatus ( rs.getString("status") );
            }
		}
		catch( Exception e )
		{
            e.printStackTrace();
			throw new ScalarException ("Error while retreiving the Distributor SO details");
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
    }

    public static ArrayList loadDistSOLineItems( DBTransaction dbTransaction, int releaseId ) throws ScalarException
    {
        ArrayList lineItemList      = new ArrayList();
        DistLineItem distLineItem   = null;
		Connection conn 		    = null;
		PreparedStatement pstmt     = null;
		ResultSet rs 			    = null;

        try
		{
            if( dbTransaction == null )
            {
                conn	= DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

			pstmt 	= conn.prepareStatement( "SELECT DIST_LINEITEM_ID, DIST_LINEITEM_NO, PRODUCT_ID, DIST_REQ_QTY, CONVERT(VARCHAR, DIST_REQ_DATE, 101) AS DIST_REQ_DATE, DIST_SHIP_QTY, CONVERT(VARCHAR, DIST_SHIP_DATE, 101) AS DIST_SHIP_DATE, UNIT_COST, EXT_COST, RESALE_PRICE,EXT_RESALE_PRICE,User_defined_Field1,User_defined_Field2 FROM DIST_LINE_ITEM_TABLE WHERE DIST_SALESORDER_ID = ?" );
            pstmt.setInt( 1, releaseId );

			rs 		= pstmt.executeQuery();

			while ( rs.next() )
			{
                distLineItem = new DistLineItem();
                distLineItem.setLineItem_Id ( rs.getInt( "DIST_LINEITEM_ID" ) );
                distLineItem.setLineItemNo  ( rs.getInt( "DIST_LINEITEM_NO" ) );
                distLineItem.setProductId   ( rs.getInt( "PRODUCT_ID" ) );
                distLineItem.setQty_req     ( rs.getInt( "DIST_REQ_QTY" ) );
                distLineItem.setReq_date    ( rs.getString( "DIST_REQ_DATE" ) );
                distLineItem.setQtyShip     ( rs.getInt( "DIST_SHIP_QTY" ) );
                distLineItem.setShipDate    ( rs.getString( "DIST_SHIP_DATE" ) );
                distLineItem.setUnitCost    ( rs.getDouble( "UNIT_COST" ) );
                distLineItem.setExtCost     ( rs.getDouble( "EXT_COST" ) );
                distLineItem.setReSalePrice ( rs.getDouble( "RESALE_PRICE" ) );
                distLineItem.setExtReSalePrice( rs.getDouble( "EXT_RESALE_PRICE" ) );
                distLineItem.setUserDefinedField1( rs.getString( "User_defined_Field1" ) );
                distLineItem.setUserDefinedField2( rs.getString( "User_defined_Field2" ) );

                lineItemList.add( distLineItem );
			}
		}
		catch( Exception e )
		{
            Logger.error( "Error while retreiving the Distributor Sales Order Line Item list", e );
			throw new ScalarException ("Error while reteiving the Distributor Sales Order Line Item list");
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
        return lineItemList;
    }

    public static ArrayList getDistributorReport ( DBTransaction dbTransaction, DistributorSearchForm searchForm ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList distInfoList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL sp_distributor_ship_details_report ( ?, ?, ?, ?, ?, ? ) }" );

            pstmt.setInt( 1, searchForm.getDistributorId() );
            pstmt.setString( 2, searchForm.getFromDate() );
            pstmt.setString( 3, searchForm.getToDate() );
            pstmt.setString( 4, "0" );
            pstmt.setString( 5, searchForm.getReportType() );
            pstmt.setString( 6, "XLREPORT" );

            rset    = pstmt.executeQuery();

            distInfoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Distributor Info Details", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return distInfoList;
    }

    public static void cancelDistributorInventory( DBTransaction dbTransaction, DistributorSalesForm salesForm) throws Exception
    {
        Connection conn 		    = null;
        CallableStatement cstmt     = null;
        boolean success             = false;
        Iterator lineItemIT         = salesForm.getLineItemList().iterator();
        DistLineItem distLineItem   = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall( "{ CALL SP_CANCEL_DIST_SALES_INVENTORY_DETAILS( ?, ?, ? ) }" );

            while( lineItemIT.hasNext() )
            {
                distLineItem = ( DistLineItem ) lineItemIT.next();

                if( distLineItem.getProductId() > 0 )
                {
                    cstmt.setString    ( 1, salesForm.getDistributorId() );
                    cstmt.setInt    ( 2, distLineItem.getProductId() );
                    cstmt.setInt    ( 3, distLineItem.getQtyShip() );

                    cstmt.executeUpdate();
                }
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            Logger.error( "Error while canceling the Distributor Sales Order details", e );
            throw new ScalarException ("Error while canceling the Distributor Sales Order details");
        }
        finally
        {
            if( dbTransaction == null )
            {
                if( success == true )
                {
                    try
                    {
                        conn.commit();
                    }
                    catch( Exception e ) { }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch( Exception e ) { }
                }
            }

            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }

    public static void adjustDistributorInventory( DBTransaction dbTransaction, DistributorInventoryForm inventoryForm) throws Exception
    {
        Connection conn 		    = null;
        CallableStatement cstmt     = null;
        boolean success             = false;
        Iterator inventoryItr         = inventoryForm.getInventoryList().iterator();
        DistributorInventory distInventory   = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall( "{ CALL SP_ADJUST_DISTRIBUTOR_INVENTORY( ?, ?, ?, ?, ? ) }" );

            while( inventoryItr.hasNext() )
            {
                distInventory = ( DistributorInventory ) inventoryItr.next();

                if( distInventory.isChecked() )
                {
                    cstmt.setInt    ( 1, distInventory.getDistributorId() );
                    cstmt.setInt    ( 2, distInventory.getProductId() );
                    cstmt.setInt    ( 3, distInventory.getLot_id() );
                    cstmt.setInt    ( 4, distInventory.getScrapQty() );
                    cstmt.setInt    ( 5, distInventory.getAdditionQty() );

                    cstmt.executeUpdate();
                }
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            Logger.error( "Error while Adjusting the Distributor Inventory details", e );
            throw new ScalarException ("Error while Adjusting the Distributor Inventory details");
        }
        finally
        {
            if( dbTransaction == null )
            {
                if( success == true )
                {
                    try
                    {
                        conn.commit();
                    }
                    catch( Exception e ) { }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch( Exception e ) { }
                }
            }

            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }

    public static String genarateAutomatedDistSalesOrderNumber( int incrementBy ) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rs 			= null;
        String salesOrderNo 	= null;
        String sequence = "00";
        Calendar cl	= Calendar.getInstance();
        String newSalesOrderNumber = StringUtils.formatDateToString(cl.getTime(), "yyMMdd" );
        //newSalesOrderNumber = newSalesOrderNumber.substring(1);
        try
        {
            conn  = DataSource.getConnection();
            pstmt = conn.prepareStatement ( "SELECT DIST_SALESORDER_ID , DIST_SALESORDER_NO FROM Dist_Sales_Order WHERE DIST_SALESORDER_ID = ( SELECT MAX( DIST_SALESORDER_ID ) FROM Dist_Sales_Order )" );
            rs    = pstmt.executeQuery();
            if ( rs.next() )
            {
                salesOrderNo = rs.getString( "DIST_SALESORDER_NO" );
            }

            if( salesOrderNo != null )
            {
                int seq = 0;
                try
                {
                    seq = Integer.parseInt ( salesOrderNo.substring( salesOrderNo.length()-2) );
                } catch ( NumberFormatException e ) { seq = -1; }
                seq = seq + incrementBy;
                if ( seq > 99 )
                {
                    seq = 0;
                }
                sequence = (seq<10) ? "0"+seq : Integer.toString(seq);
            }
            newSalesOrderNumber = newSalesOrderNumber + sequence;

            while ( isDistSalesOrderNumberExists(null, newSalesOrderNumber) )
            {
                int seq = 0;
                try
                {
                    seq = Integer.parseInt ( newSalesOrderNumber.substring( newSalesOrderNumber.length()-2) );
                } catch ( NumberFormatException e ) { seq = -1; }
                seq = seq + incrementBy;
                if ( seq > 99 )
                {
                    seq = 0;
                }
                sequence = (seq<10) ? "0"+seq : Integer.toString(seq);

                newSalesOrderNumber = newSalesOrderNumber + sequence;
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Automated Distributor Sales Order No from database", sqlex);
        }

        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return newSalesOrderNumber;
    }

    public static boolean isDistSalesOrderNumberExists ( DBTransaction dbTransaction, String releaseNumber ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean isExists        = false;

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "SELECT COUNT(*) FROM Dist_Sales_Order WHERE DIST_SALESORDER_NO = ?" );
            pstmt.setString ( 1, releaseNumber );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                int count = rset.getInt ( 1 );
                if ( count > 0 )
                {
                    isExists = true;
                }
            }
        }
        catch ( Exception  e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not check the Dist Sales Order number", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return isExists;
    }
}
